package ee.maoinvest.backend.model.dao.postgresqlimplementations;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import org.apache.log4j.Logger;

import ee.maoinvest.backend.database.ConnectionManager;
import ee.maoinvest.backend.model.dao.ElectricityMeterReadingDAO;
import ee.maoinvest.backend.model.data.ElectricityMeterReading;

public class ElectricityMeterReadingDAOPostgresqlImpl implements ElectricityMeterReadingDAO
{
	
	Logger logger = Logger.getLogger(ElectricityMeterReadingDAOPostgresqlImpl.class);
	Connection dbConnection;
	String sql;
	PreparedStatement preparedStatement;
	Statement statement;
	ResultSet resultSet;
	private final int WARNING_NO_ROWS_AFFECTED = 0;
	private int rowsAffected;
	
	public ElectricityMeterReadingDAOPostgresqlImpl(Connection dbConnection)
	{
		this.dbConnection = dbConnection;
	}

	@Override
	public int insertElectricityMeterReading(
			ElectricityMeterReading electricityMeterReading) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "INSERT INTO Electricity_meter_readings (electricityMeterFK, " + 
				"daytimeReading, nightTimeReading, reactivePowerReading, " + 
				"dateReadingsWereTaken) " + 
				"VALUES " + 
				"(?, " +  
				"?, ?, ?, " +  
				"?)";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
			preparedStatement.setInt	(1, electricityMeterReading.getElectricityMeterFK());
			preparedStatement.setObject	(2, electricityMeterReading.getDaytimeReading(), Types.NUMERIC);
			preparedStatement.setObject	(3, electricityMeterReading.getNightTimeReading(), Types.NUMERIC);
			preparedStatement.setObject	(4, electricityMeterReading.getReactivePowerReading(), Types.NUMERIC);
			preparedStatement.setDate	(5, electricityMeterReading.getDateReadingsWereTaken());
			
			logger.info(preparedStatement.toString());
			rowsAffected = preparedStatement.executeUpdate();
			
			if (rowsAffected == 1)
			{
				resultSet = preparedStatement.getGeneratedKeys();
				resultSet.next(); //answer consist only 1 row, so move the cursor just once to start to process the result
				int newId = resultSet.getInt(1); // 1, because identifiers are held in the first column 
				electricityMeterReading.setElectricityMeterReadingID(newId);
				return rowsAffected;
			}
			else
			{
				System.out.println("No rows affected!");
				logger.info("Unsuccessful insertion to a database: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			return WARNING_NO_ROWS_AFFECTED;
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}

	@Override
	public ElectricityMeterReading getElectricityMeterReadingByID(
			int electricityMeterReadingID) 
	{
		resultSet = null;
		ElectricityMeterReading electricityMeterReadingRetrieved = null;
		sql = "SELECT * FROM Electricity_meter_readings WHERE electricityMeterReadingID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
					);
			//filling the placeholders 
			preparedStatement.setInt(1, electricityMeterReadingID);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of a received data set (so it can start to read and process data)
			resultSet.beforeFirst();
			
			if (resultSet.next())
			{
				//setObject() is used, when column or field value in java, can also be null.
				electricityMeterReadingRetrieved = fillElectricityMeterReadingWithDataRetrievedFromDB();
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return electricityMeterReadingRetrieved;
	}

	@Override
	public int updateElectricityMeterReading(
			ElectricityMeterReading electricityMeterReading) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "UPDATE Electricity_meter_readings " + 
				"SET electricityMeterFK = ?, " + 
				"daytimeReading = ?, nightTimeReading = ?, reactivePowerReading = ?, " + 
				"dateReadingsWereTaken = ? " + 
				"WHERE electricityMeterReadingID = ?;"; 
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			
			preparedStatement.setInt	(1, electricityMeterReading.getElectricityMeterFK());
			preparedStatement.setObject	(2, electricityMeterReading.getDaytimeReading(), Types.NUMERIC);
			preparedStatement.setObject	(3, electricityMeterReading.getNightTimeReading(), Types.NUMERIC);
			preparedStatement.setObject	(4, electricityMeterReading.getReactivePowerReading(), Types.NUMERIC);
			preparedStatement.setDate	(5, electricityMeterReading.getDateReadingsWereTaken());
			preparedStatement.setInt	(6, electricityMeterReading.getElectricityMeterReadingID());
			
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Update sucessful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.error("Update was unsuccesful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
			return WARNING_NO_ROWS_AFFECTED;		
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}

	@Override
	public int deleteElectricityMeterReading(int electricityMeterReadingID) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "DELETE FROM Electricity_meter_readings WHERE electricityMeterReadingID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			preparedStatement.setInt(1, electricityMeterReadingID);
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Deletion successful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.info("Deletion unsuccessful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch(SQLException e) 
		{
			e.printStackTrace();
			return WARNING_NO_ROWS_AFFECTED;
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}
	
	@Override
	public ElectricityMeterReading getElectricityMeterLastReadings(
			int electricityMeterID) 
	{
		resultSet = null;
		ElectricityMeterReading electricityMeterReadingRetrieved = null;
		sql = "SELECT * FROM Electricity_meter_readings " +
				"WHERE electricityMeterReadingID = " +
				"(SELECT max(electricityMeterReadingID) AS latestReadingsID FROM Electricity_meter_readings " +
				"WHERE electricityMeterFK = ?);";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
					);
			//filling the placeholders 
			preparedStatement.setInt(1, electricityMeterID);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of a received data set (so it can start to read and process data)
			resultSet.beforeFirst();
			
			if (resultSet.next())
			{
				//setObject() is used, when column or field value in java, can also be null.
				electricityMeterReadingRetrieved = fillElectricityMeterReadingWithDataRetrievedFromDB();
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return electricityMeterReadingRetrieved;
	}
	
	//-----------------HELPER METHODS----------------------------------
	
	private ElectricityMeterReading fillElectricityMeterReadingWithDataRetrievedFromDB()
	{
		ElectricityMeterReading electricityMeterReadingRetrieved = null;
		
		try 
		{
			electricityMeterReadingRetrieved = new ElectricityMeterReading();
			electricityMeterReadingRetrieved.setElectricityMeterReadingID(resultSet.getInt("electricityMeterReadingID"));
			electricityMeterReadingRetrieved.setElectricityMeterFK(resultSet.getInt("electricityMeterFK"));
			electricityMeterReadingRetrieved.setDaytimeReading((BigDecimal) resultSet.getObject("daytimeReading"));
			electricityMeterReadingRetrieved.setNightTimeReading((BigDecimal) resultSet.getObject("nightTimeReading"));
			electricityMeterReadingRetrieved.setReactivePowerReading((BigDecimal) resultSet.getObject("reactivePowerReading"));
			electricityMeterReadingRetrieved.setDateReadingsWereTaken((Date) resultSet.getObject("dateReadingsWereTaken"));
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		return electricityMeterReadingRetrieved;
	}
}
